CREATE FUNCTION dbo.fn_Harvest_EMail (@s VARCHAR(255)) RETURNS VARCHAR(255) AS BEGIN
DECLARE @email VARCHAR(255); SET @email= Null
DECLARE @ptr INT, @i INT, @j INT, @k INT

  IF @s LIKE '%@%.%' BEGIN	-- there's likely an email address in here somewhere

	IF @s NOT LIKE '%[ \\/:*"''<>]%' ESCAPE '\'	-- check for clean email address only
	  SET @email= @s
	ELSE BEGIN

	  -- otherwise dig deeper
	  SET @ptr= CHARINDEX('@', @s)
	  SET @k= LEN(@s)

	  WHILE 0 < @ptr AND @ptr < @k BEGIN
		SELECT @i= @ptr, @j= @ptr
		WHILE  1 < @i AND SUBSTRING(@s, @i-1, 1) NOT IN (' ', '\', '/', ':', '*', '"', '''', '@', '<', '>') SET @i= @i-1	-- go backward from the "@" to a token break or end of string
		WHILE @j < @k AND SUBSTRING(@s, @j+1, 1) NOT IN (' ', '\', '/', ':', '*', '"', '''', '@', '<', '>') SET @j= @j+1	-- go forwards from the "@" to a token break or end of string
		SET @email= SUBSTRING(@s, @i, (@j-@i+1))
		IF @email LIKE '%@%.%' SET @ptr= 0	-- got it!
		ELSE BEGIN
		  SET @email= Null
		  SET @ptr= CHARINDEX('@', @s, @j)	-- try again
		  IF @ptr <= @j SET @ptr= 0
		END
	  END

	END
  END
  RETURN @email
END
GO